package by.sjc.giz.dao.repositiory.impl;

import by.sjc.giz.dao.base.AbstractHibernateDao;
import by.sjc.giz.dao.entity.BookEntity;
import by.sjc.giz.dao.entity.SubjectEntity;
import by.sjc.giz.dao.entity.publisher.ContractWithAuthorsEntity;
import by.sjc.giz.dao.entity.publisher.DiscountEntity;
import by.sjc.giz.dao.repositiory.PublisherDao;
import by.sjc.giz.dao.repositiory.SubjectDao;
import by.sjc.giz.model.BooksFilter;
import by.sjc.giz.model.SubjectType;
import org.apache.log4j.Logger;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.criterion.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.util.ArrayList;
import java.util.List;

/**
 * Created by Z on 11.08.14.
 */
@Repository
@Transactional
public class PublisherRepository  extends AbstractHibernateDao<SubjectEntity, Long> implements PublisherDao {

    public static final Logger logger = Logger.getLogger(PublisherRepository.class);

    @Autowired
    SubjectDao subjectDao;


    @SuppressWarnings("unchecked")
    private List<ContractWithAuthorsEntity> getContractsByIdList(List<Integer> idList) {
        if (idList==null || idList.size()==0)
            return new ArrayList<ContractWithAuthorsEntity>(1);

        return getSession().createCriteria(ContractWithAuthorsEntity.class)
                .add( Restrictions.in("id", idList))
                .setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY)
                .list();
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<SubjectEntity> getPublisherList() {
        logger.debug("getting all publishers");

        @SuppressWarnings("JpaQlInspection")
        String hql = "from SubjectEntity a where type = 'PUBLISHER'";

        return getSession().createQuery(hql).list();
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<BookEntity> getPublisherBookList(int publisherId) {
        @SuppressWarnings("JpaQlInspection")
        String hql = "from BookEntity where publisher_id=:publisherId";
        return getSession().createQuery(hql)
                .setParameter("publisherId",publisherId)
                .list();
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<ContractWithAuthorsEntity> getContractWithAuthorsList(int publisherId) {
        logger.info("contractsWithAuthorList:::::::::::");

        @SuppressWarnings("JpaQlInspection")
        String hql = "from ContractWithAuthorsEntity"+
                     "  where book_id in"+
                     " (select book.id from BookEntity book where publisher_id=:publisherId) ";
        List<ContractWithAuthorsEntity> res = getSession().createQuery(hql)
                .setParameter("publisherId",publisherId)
                .list();

        logger.info("cnt="+res.size());
        logger.info(res.toString());
        return res;
    }

    @Override
    public void addContractWithAuthors(ContractWithAuthorsEntity contractEntity) {
        logger.info("saving =" + contractEntity);
        getSession().saveOrUpdate(contractEntity);
        logger.info(":::::::::::::::::end saving:::::::::::::::");
    }

    @Override
    public float getBookPrice(int bookId) {
        @SuppressWarnings("JpaQlInspection")
        String hql = "select sellPrice from ContractWithAuthorsEntity where book_id = :id";
        return (Float) getSession().createQuery(hql)
                    .setParameter("id",bookId)
                    .uniqueResult();
    }

    private Criteria createBaseCriteria(BooksFilter filter) {
        Criteria criteria = getSession().createCriteria(ContractWithAuthorsEntity.class)
                .createAlias("book", "book");

        if (filter.getGenres() != null) {
            criteria.createCriteria("book.genreEntitySet","genre")
                    .add( Restrictions.in("genre.name", filter.getGenres()) );
        }
        if (filter.getAuthors() != null) {
            criteria.createCriteria("book.authorEntitySet","author")
                    .add( Restrictions.in("author.name", filter.getAuthors()) );
        }
        if (filter.getPublishers() != null) {
            criteria.createCriteria("book.publisher","publisher")
                    .add( Restrictions.in("publisher.name", filter.getPublishers()) )
                    .add( Restrictions.eq("publisher.type", SubjectType.PUBLISHER) );
        }
        if (filter.getMinPrice() != null) {
            criteria.add( Restrictions.ge("this.sellPrice", filter.getMinPrice()) );
        }
        if (filter.getMaxPrice() != null) {
            criteria.add( Restrictions.le("this.sellPrice", filter.getMaxPrice()) );
        }
        if (filter.getName() != null) {
            criteria.add( Restrictions.ilike("book.name",filter.getName(), MatchMode.ANYWHERE) );
        }
        return criteria;
    }


    @SuppressWarnings("unchecked")
    @Override
    public List<ContractWithAuthorsEntity> getContracts(int pageSize, int pageNumber, BooksFilter filter) {
        Criteria criteria = createBaseCriteria(filter)
                .setProjection(Projections.groupProperty("this.id"));

        if (pageSize > 0) {
            criteria.setMaxResults(pageSize)
                    .setFirstResult(pageSize * (pageNumber - 1));
        }

        List<Integer> idList = criteria.list();
        logger.info("ContractWithAuthorsEntity id list =" + idList);
        return getContractsByIdList(idList);
    }

    @Override
    public int getContractsCount(BooksFilter filter) {
        Criteria criteria = createBaseCriteria(filter)
                .setProjection( Projections.countDistinct("this.id") );
        return ((Long) criteria.uniqueResult()).intValue();
    }

    @Override
    public DiscountEntity getDiscount(int publisherId, int clientId) {
        Criteria criteria = getSession().createCriteria(DiscountEntity.class)
                .add(Restrictions.eq("publisher.id", publisherId))
                .add(Restrictions.eq("client.id",clientId));
        return (DiscountEntity) criteria.uniqueResult();
    }

    @Override
    public void saveOrUpdateDiscountEntity(DiscountEntity discountEntity) {
        getSession().saveOrUpdate(discountEntity);
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<DiscountEntity> getDiscountsList(int publisherId, int pageSize, int pageNumber) {
        String sql = "SELECT shop.id as shopId, sum(ob.book_price*ob.count) as spendMoney, pd.id as pdId, pd.value " +
                "FROM subjects shop " +
                "LEFT JOIN orders o ON shop.id=o.sender_id " +
                "LEFT JOIN order_books ob ON ob.order_id=o.id " +
                "LEFT JOIN subjects publisher ON publisher.id=o.recipient_id " +
                "LEFT JOIN publisher_discounts pd ON (pd.publisher_id=publisher.id AND pd.client_id=shop.id) " +
                "WHERE shop.type='SHOP' AND (publisher.id=:publisherId OR publisher.id IS NULL) " +
                "GROUP BY shop.id, publisher.id " +
                "ORDER BY pd.value DESC, spendMoney DESC, shop.id";

        Query query = getSession().createSQLQuery(sql)
                .setParameter("publisherId", publisherId);

        if (pageSize>0) {
            query.setMaxResults(pageSize)
                 .setFirstResult(pageSize * (pageNumber - 1));
        }

        List<Object[]> res = (List<Object[]>) query.list();
        if (res != null) {
            List<DiscountEntity> discounts = new ArrayList<DiscountEntity>( res.size() );
            SubjectEntity publisher = subjectDao.getSubjectById(publisherId);
            for (Object[] entry : res) {
//                logger.info("shopId="+ entry[0]);
//                logger.info("spendMoney="+ entry[1]);
//                logger.info("discount_id="+ entry[2]);
//                logger.info("discount="+ entry[3]);
                DiscountEntity discountEntity = new DiscountEntity();

                if (entry[2]!=null)
                    discountEntity.setId((Integer) entry[2]);

                if (entry[3]!=null)
                    discountEntity.setValue((Integer) entry[3]);
                else
                    discountEntity.setValue(0);

                if (entry[1]!=null)
                    discountEntity.setSpendMoney( ((Double) entry[1]).floatValue() );
                else
                    discountEntity.setSpendMoney(0);

                discountEntity.setClient( subjectDao.getSubjectById((Integer) entry[0]) );
                discountEntity.setPublisher(publisher);

                discounts.add(discountEntity);
            }
            return discounts;
        } else {
            return new ArrayList<DiscountEntity>( 1 );
        }
    }
}
